package com.ucsd.cse135.gas;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.ucsd.cse135.db.Database;

public class Applicant {
	
	private int applicantId;
	private String name;
	private String firstName;
	private String middleInitial;
	private String countryOfCitizenship;
	private int countryOfCitizenshipId;
	private String countryOfResidence;
	private int countryOfResidenceId;
	private String street;
	private String city;
	private String countryPhoneCode;
	private String state;
	private int zip;
	private int areaCode;
	private int number;
	private boolean usResidence;
	private boolean domestic;
	private String specialization;
	private int specializationId;
	private String mail;
	
	
	public String getMail() {
		return mail;
	}
	public void setMail(String mail) {
		this.mail = mail;
	}
	public int getApplicantId() {
			return applicantId;
	}
	public void setApplicantId(int applicantId) {		
				
		this.applicantId = applicantId;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getMiddleInitial() {
		return middleInitial;
	}
	public void setMiddleInitial(String middleInitial) {
		this.middleInitial = middleInitial;
	}
	public String getCountryOfCitizenship() {
		return countryOfCitizenship;
	}
	public void setCountryOfCitizenship(String countryOfCitizenship) {
		this.countryOfCitizenship = countryOfCitizenship;
	}
	public int getCountryOfCitizenshipId() {
		return countryOfCitizenshipId;
	}
	public void setCountryOfCitizenshipId(int countryOfCitizenshipId) {
		this.countryOfCitizenshipId = countryOfCitizenshipId;
	}
	public String getCountryOfResidence() {
		return countryOfResidence;
	}
	public void setCountryOfResidence(String countryOfResidence) {
		this.countryOfResidence = countryOfResidence;
	}
	public int getCountryOfResidenceId() {
		return countryOfResidenceId;
	}
	public void setCountryOfResidenceId(int countryOfResidenceId) {
		this.countryOfResidenceId = countryOfResidenceId;
	}
	public String getStreet() {
		return street;
	}
	public void setStreet(String street) {
		this.street = street;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getCountryPhoneCode() {
		return countryPhoneCode;
	}
	public void setCountryPhoneCode(String countryPhoneCode) {
		this.countryPhoneCode = countryPhoneCode;
	}
	public String getState() {
		return state;
	}
	public void setState(String state) {
		this.state = state;
	}
	public int getZip() {
		return zip;
	}
	public void setZip(int zip) {
		this.zip = zip;
	}
	public int getAreaCode() {
		return areaCode;
	}
	public void setAreaCode(int areaCode) {
		this.areaCode = areaCode;
	}
	public int getNumber() {
		return number;
	}
	public void setNumber(int number) {
		this.number = number;
	}
	public boolean getUsResidence() {
		return usResidence;
	}
	public void setUsResidence(boolean usResidence) {
		this.usResidence = usResidence;
	}
	public boolean isDomestic() {
		return domestic;
	}
	public void setDomestic(boolean domestic) {
		this.domestic = domestic;
	}
	public String getSpecialization() {
		return specialization;
	}
	public void setSpecialization(String specialization) {		
		
		this.specialization = specialization;
		
		try {
			
			Connection conn = Database.open();
		
			/** Set specialization id by comparing the name **/
			
			String sql = "SELECT id FROM specialization WHERE name = ?";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, specialization);
			
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			
			this.specializationId = rs.getInt("id");
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
	}
	public int getSpecializationId() {
		return specializationId;
	}
	public void setSpecializationId(int specializationId) {
		this.specializationId = specializationId;
	}
	
	/** Method to write everything to the database **/
	
	public void writeToDatabase() {
		
		try {
			
			Connection conn = Database.open();
		
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT nextval('applicant_id_seq');");
			rs.next();
			this.setApplicantId(rs.getInt("nextval"));
			
			String sql = "INSERT INTO applicant (id, name, first_name, middle_initial, street, city, state, country_phone_code, " +
						 "zip, area_code, number, us_residence, country_of_citizenship, country_of_residence, specialization, mail) VALUES " +
						 "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ";  
		
			PreparedStatement preStmt = conn.prepareStatement(sql);
			
			preStmt.setInt(1, this.getApplicantId());
			preStmt.setString(2, this.getName());
			preStmt.setString(3, this.getFirstName());
			preStmt.setString(4, this.getMiddleInitial());
			preStmt.setString(5, this.getStreet());
			preStmt.setString(6, this.getCity());
			
			if(this.getState() != null)
			{
				preStmt.setString(7, this.getState());
				preStmt.setString(8, "");
			}
			else
			{
				preStmt.setString(7, "");
				preStmt.setString(8, this.getCountryPhoneCode());
			}
			
			preStmt.setInt(9, this.getZip());
			preStmt.setInt(10, this.getAreaCode());
			preStmt.setInt(11, this.getNumber());
		
			if(this.getUsResidence())
			{
				preStmt.setInt(12, 1);
			}
			else
			{
				preStmt.setInt(12, 0);
			}
			preStmt.setInt(13, this.getCountryOfCitizenshipId());
			preStmt.setInt(14, this.getCountryOfResidenceId());
			preStmt.setInt(15, this.getSpecializationId());
			preStmt.setString(16, this.getMail());
			
			preStmt.executeUpdate();
			
			Database.close();

		}
		catch(Exception e)
		{
			Database.close();
		}
	}
	
	/** Method to register a new user **/
	
	public int register(String mail, String password) {
		
		Connection conn = Database.open();
			
		/** First enter user name (mail) and password into users table **/
		
		try {
			String sql = "INSERT INTO users VALUES (?, md5(?));";
		
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, mail);
			preStmt.setString(2, password);
			preStmt.executeUpdate();
			preStmt.close();
		}
		catch(SQLException se)
		{
			Database.close();
			se.getCause();
			return 203;
		}
		catch(Exception e)
		{
			Database.close();
			e.getCause();
			return 200;
		}
			
		/** Then map the role applicant on the new user **/
		
		try  {
			PreparedStatement preStmt = conn.prepareStatement("INSERT INTO user_role VALUES (?, 'applicant');");
			preStmt.setString(1, mail);			
			preStmt.executeUpdate();
			
			preStmt.close();
			Database.close();
			
			return 101;
		}
		catch(Exception e)
		{
			Database.close();
			e.getCause();
			return 200;
		}
	}

	/** Method to get the applicant id **/
	
	public int getApplicantId(String mail) {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "SELECT id FROM applicant WHERE mail = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, mail);
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			return rs.getInt("id");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return -1;
		}
		
	}

	/** Method to fill an applicant object with data stored in the database **/
	
	public void setApplicant(int id) {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "SELECT * FROM applicant WHERE id = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setInt(1, id);
			
			ResultSet rs = preStmt.executeQuery();
			rs.next();
			
			this.setApplicantId(id);
			this.setName(rs.getString("name"));
			this.setFirstName(rs.getString("first_name"));
			this.setMiddleInitial(rs.getString("middle_initial"));
			this.setCountryOfCitizenshipId(rs.getInt("country_of_citizenship"));
			this.setCountryOfResidenceId(rs.getInt("country_of_residence"));
			this.setMail(rs.getString("mail"));
			this.setStreet(rs.getString("street"));
			this.setCity(rs.getString("city"));
			this.setCountryPhoneCode(rs.getString("country_phone_code"));
			this.setState(rs.getString("state"));
			this.setZip(rs.getInt("zip"));
			this.setAreaCode(rs.getInt("area_code"));
			this.setNumber(rs.getInt("number"));
			
			if(rs.getInt("us_residence") == 1)
			{
				this.setUsResidence(true);
			}
			else
			{
				this.setUsResidence(false);
			}
			
			this.setSpecializationId(rs.getInt("specialization"));
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
}
